# FileName : DBHandle.py
# Author   : Anzhijun
# DateTime : 2021/05/08 2:03 PM
# SoftWare : PyCharm


import pymysql

from common.yaml_handler import yaml_data

'''
此公共类主要是 数据库的增删改查


'''
class DataBaseHandle(object):
    ''' 定义一个mysql的操作类'''

    def __init__(self):
        ''' 初始化数据库信息并创建数据库连接'''
        self.db = pymysql.connect(host=yaml_data['mysql']['host'], user=yaml_data['mysql']['user'],
                                  password=yaml_data['mysql']['password'],
                                  database=yaml_data['mysql']['database'])  # 远程数据库

    # 查询数据的方法
    def selectDb(self, Variable_name,**kwargs):
        '''数据库查询'''
        self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)
        try:

            sql = "select *  from  ky_test_env_common_variable where Variable_name='%s'" % Variable_name
            self.cursor.execute(sql)  # 执行查询语句
            data = self.cursor.fetchall()  # # 返回 查询数据 条数 可以根据 返回值 判定处理结果
            # print(data)
            return data
        except Exception as e:
            print('查询数据失败原因为：%s' % e)

        finally:
            self.cursor.close()  # 关闭游标

    # 新增数据的方法
    def insertDB(self, sql, Variable_name,Variable_value,Variable_name_remark):
        ''' 数据库新增数据  插入数据
        说明： 如果插入的数据已存在 则更新数据
        '''
        self.cursor = self.db.cursor()
        try:
            select_sql = "select *  from  ky_test_env_common_variable where Variable_name='%s'" % Variable_name
            print(select_sql)
            data = self.cursor.execute(select_sql)
            print('查询时间',data)
            if data != 0:
                '''
                如果插入的数据已存在 则更新该数据（调用更新接口）
                '''
                print("变量名称：%s 已存在无法新增,正在为您更新该数据" % Variable_name)
                update_sql ="UPDATE  ky_common_variable  SET Variable_value='{0}',Variable_remark='{1}' WHERE Variable_name ='{2}'".format(Variable_value,Variable_name_remark,Variable_name)
                print(update_sql)
                DataBaseHandle().updateDb(update_sql,Variable_name,Variable_value,Variable_name_remark)

            else:
                self.cursor.execute(sql)  # 执行插入语句命令
                self.db.commit()  # 提交到数据库执行
                # print("新增成功")
        except Exception as e:
            print('数据插入失败，原因为：%s' % e)
            self.db.rollback()  # 插入失败需要回滚操作
        finally:
            self.cursor.close()  # 关闭游标

    # 更新数据的方法
    def updateDb(self, sql,Variable_name,Variable_value,Variable_name_remark):
        ''' 更新数据 对已维护的数据进行更新操作
        如果更新的数据不存在则新建该数据'''
        self.cursor = self.db.cursor()
        select_sql = "select *  from  ky_test_env_common_variable where Variable_name='%s'" % Variable_name
        data = self.cursor.execute(select_sql)

        try:
            if data != 0 :
                self.cursor.execute(sql)  # 执行更新的sql语句
                self.db.commit()  # 提交到数据库执行更新操作
                # print('数据更新成功')
            else:

                # print("变量名称：%s 不存在，正在为您新建该数据,请查看数据库" % Variable_name)
                insert_sql = "INSERT INTO ky_test_env_common_variable (Variable_name,Variable_value,Variable_remark) VALUES ('{0}','{1}','{2}')".format(Variable_name,Variable_value,Variable_name_remark)
                DataBaseHandle().insertDB(insert_sql, Variable_name,Variable_value,Variable_name_remark)


        except Exception as e:
            print("数据更新失败，原因为：%s" % e)
            self.db.rollback()  # 更新失败做回滚操作
        finally:
            self.cursor.close()

    def editeDb(self,Variable_name,Variable_value,Variable_name_remark):
        '''
         公共方法 编辑数据 如果数据不存在则新增数据，已存在则编辑数据

        '''
        self.cursor = self.db.cursor()
        try:
            select_sql = "select *  from  ky_test_env_common_variable where Variable_name='%s'" % Variable_name
            # print(select_sql)
            data = self.cursor.execute(select_sql)
            # print('查询时间',data)
            if data != 0:   # 查询的数据存在 则更新数据
                '''
                如果查询的数据已存在 则更新该数据
                '''
                # print("变量名称：%s 已存在无法新增,正在为您更新该数据" % Variable_name)
                update_sql ="UPDATE  ky_test_env_common_variable  SET Variable_value='{0}',Variable_remark='{1}' WHERE Variable_name ='{2}'".format(Variable_value,Variable_name_remark,Variable_name)
                # print(update_sql)
                DataBaseHandle().updateDb(update_sql,Variable_name,Variable_value,Variable_name_remark)

            else: # 查询的数据不存在 则插入数据
                try:
                    insert_sql = "INSERT INTO ky_test_env_common_variable (Variable_name,Variable_value,Variable_remark) VALUES ('{0}','{1}','{2}')".format(Variable_name,Variable_value,Variable_name_remark)
                    # print(insert_sql)
                    self.cursor.execute(insert_sql)  # 执行插入语句命令
                    self.db.commit()  # 提交到数据库执行
                    print("新增成功")

                except Exception as e:
                    print('数据插入失败，原因为：%s' % e)
                    self.db.rollback()  # 插入失败需要回滚操作
        except Exception as e:
            print('数据编辑失败，原因为：%s' % e)
            self.db.rollback()  # 插入失败需要回滚操作
        finally:
            self.cursor.close()  # 关闭游标



    # 删除数据的方法
    def deleteDb(self, sql):
        self.cursor = self.db.cursor()
        try:
            self.cursor.execute(sql)  # 执行删除的语句
            self.db.commit()  # 提交到数据执行删除操作
            print('数据删除成功')
        except Exception as e:
            print("删除失败，原因为：%s" % e)
            self.db.rollback()  # 删除失败 数据执行回滚操作
        finally:
            self.cursor.close()  # 关闭游标

    # 数据库关闭的功能
    def Dbclose(self):
        '''数据库连接关闭'''
        self.db.close()


if __name__ == '__main__':
    DbHandle = DataBaseHandle()
    data = DataBaseHandle().selectDb(Variable_name='Authorization')
    print(data[0]['Variable_value'])

    # 单元测试使用

    # Variable_name = 'tms_header'
    # sql5 = "INSERT INTO ky_common_variable (Variable_name,Variable_value,Variable_remark) VALUES (Variable_name,'Variable_name','请求头的签名')"
    # data3 = DbHandle.insertDB(sql5, Variable_name)
    # # 查询数据
    # sql = "select *  from  ky_common_variable where Variable_name='sign'"
    # data2 = DbHandle.selectDb(sql)
    # print(data2)

    # # # # # 新增数据测试
    # Variable_name='sign'
    # sign ='sign'
    # sign_value ='8BBDB5C6DEB2849B36EEA80DA4AC2039'
    # # print(a)
    # sql = "INSERT INTO ky_common_variable (Variable_name,Variable_value,Variable_remark) VALUES ('sign','{0}','请求头的签名')".format(sign_value)
    # # print(sql)
    # data3 = DbHandle.insertDB(sql,Variable_name)


    # 更新的数据不存在  系统自动新增数据
    # Variable_name = 'soid'
    # Variable_value = '354468888'
    # Variable_name_remark = '运单id555'
    # sql = "UPDATE  ky_common_variable  SET Variable_value='{0}',Variable_remark='{1}' WHERE Variable_name ='{2}'".format(Variable_value, Variable_name_remark, Variable_name)
    # DbHandle.updateDb(sql, Variable_name, Variable_value, Variable_name_remark)

    # # 系统插入的数据已存在
    # Variable_name = 'soid01'
    # Variable_value = '3544655'
    # Variable_name_remark = '运单id 插入的数据已存在111'
    # sql = "INSERT INTO ky_common_variable (Variable_name,Variable_value,Variable_remark) VALUES ('{0}','{1}','{2}')".format(Variable_name,Variable_value,Variable_name_remark)
    # DbHandle.insertDB(sql, Variable_name, Variable_value, Variable_name_remark)
    # DbHandle.editeDb('YD','2021','运单id152')  # 单元测试

    # 关闭数据库
    DbHandle.Dbclose()


'''
    # 单元测试使用
    # # 查询数据
    # sql = "select *  from  ky_common_variable where Variable_name='sign'"
    # data2 = DbHandle.selectDb(sql)
    # print(data2)
    #
    # # # 新增数据测试
    # Variable_name='sign'
    # sql = "INSERT INTO ky_common_variable (Variable_name,Variable_value,Variable_remark) VALUES ('sign','8BBDB5C6DEB2849B36EEA80DA4AC2039','请求头的签名')"
    # data3 = DbHandle.insertDB(sql,Variable_name)

    # # # 更改数据
    # Variable_name = '111'
    # sql = "UPDATE  ky_common_variable  SET Variable_value='888',Variable_remark='更新费用1的金额' WHERE Variable_name ='fee1'"
    # DbHandle.updateDb(sql,Variable_name)

    # # 删除数据
    # sql = "DELETE  from  ky_common_variable where Variable_name='sign'"
    # DbHandle.deleteDb(sql)

'''

